package org.kb;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class DerbyTest {
	public static void main(String[] args) {
		createDreamDB();
	}

	private static void createDreamDB() {
		try {
			Class.forName("org.apache.derby.jdbc.EmbeddedDriver").newInstance();// 加载驱动
			Connection conn = DriverManager
					.getConnection("jdbc:derby:d:/DREAMDB;create=true;territory=zh_CN");// 连接数据库
			Statement st = conn.createStatement();
			/** daydeal */
			String sql = "create table daydeal ( id  char(32)  not null, code  varchar(10),"
					+ "name varchar(100),dealdate varchar(19),startPrice  float ,"
					+ " HIGH float,LOW float,endPrice float,"
					+ "VOL float,riseWidth float,dayNum  int,VOLRiseWidth float,"
					+ "df char(1),ts varchar(19),constraint PK_DAYDEAL primary key  (id))";
			st.execute(sql);
			/** dealrecord */
			sql = "create table dealrecord (id     char(32)   not null,happenDate char(10),"
					+ " happenTime char(8) ,businessName  varchar(20) ,stockCode  varchar(10) ,stockName  varchar(100),"
					+ " dealPrice  float,dealNum int,dealAmount float,stockBalance  float,commissionCharge     float,"
					+ "  stampTax   float,transferCharge float,otherCharge float,happenCharge  float,"
					+ " fundsBalance  float,entrustCode varchar(10) ,entrustPrice  float,"
					+ " entrustNum int,stockholderCode      varchar(20) ,fundsAccount  varchar(20) ,"
					+ " moneyKind  varchar(20) ,dealGroup  int,df     char(1) ,ts     varchar(19) ,"
					+ " constraint PK_DEALRECORD primary key  (id))";
			st.execute(sql);
			/** stock */
			sql = " create table stock (id     char(32)   not null,code   varchar(10) ,name   varchar(100),"
					+ " df     char(1) ,ts     varchar(19) ,myflag char(1) , constraint PK_STOCK primary key  (id))";
			st.execute(sql);

		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	private static void findDate() {
		try {
			Class.forName("org.apache.derby.jdbc.EmbeddedDriver").newInstance();// 加载驱动
			Connection conn = DriverManager
					.getConnection("jdbc:derby:DREAMDB;create=false;territory=zh_CN");// 连接数据库
			Statement st = conn.createStatement();
			/** daydeal */
//			st.executeUpdate("insert into daydeal(ID,code,name) values ('1','00628','中国石化')");// 插入数据
//			st.executeUpdate("insert into daydeal(ID,code,name) values ('2','00629','zhong')");// 插入数据
			ResultSet rs = st.executeQuery("select * from daydeal");// 读取刚插入的数据
			while (rs.next()) {
				String id = rs.getString("code");
				String name = rs.getString("name");
				System.out.println("code=" + id);
				System.out.println("NAME=" + name);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	private static void test() {
		try {
			Class.forName("org.apache.derby.jdbc.EmbeddedDriver").newInstance();// 加载驱动
			Connection conn = DriverManager
					.getConnection("jdbc:derby:TESTDB;create=true");// 连接数据库
			Statement st = conn.createStatement();
			st.execute("create table USER_INFO (ID INT NOT NULL,NAME VARCHAR(10) NOT NULL)");// 建表
			st.executeUpdate("insert into USER_INFO(ID,NAME) values (1,'hermit')");// 插入数据
			st.executeUpdate("insert into USER_INFO(ID,NAME) values (2,'test')");// 插入数据
			ResultSet rs = st.executeQuery("select * from USER_INFO");// 读取刚插入的数据
			while (rs.next()) {
				int id = rs.getInt(1);
				String name = rs.getString(2);
				System.out.println("ID=" + id);
				System.out.println("NAME=" + name);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
}
